
if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_pu_tong')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_pu_tong
go


create procedure bd_proc_schedule_pu_tong (@user_serial_str varchar(max),@begin_date date,@end_date date,@ban_ci varchar(max),@ip varchar(max),@operator varchar(max),@doType int = 0) as
BEGIN

-- 定义返回受影响行数结果
	declare @result int = 0;


	--  异常扑捉机制
		BEGIN TRY

			CREATE TABLE #user_serial(
				id INT IDENTITY,
				user_serial VARCHAR(20) COLLATE Chinese_PRC_CI_AS
			)

			CREATE TABLE #result(
			  user_no varchar(200) COLLATE Chinese_PRC_CI_AS,
			  user_name varchar(200) COLLATE Chinese_PRC_CI_AS,
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  dept_name varchar(200) COLLATE Chinese_PRC_CI_AS,
			  dept_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  col_name VARCHAR(20) COLLATE Chinese_PRC_CI_AS,
			  value VARCHAR(100) COLLATE Chinese_PRC_CI_AS,
			  date DATE,
			  year_month VARCHAR(10) COLLATE Chinese_PRC_CI_AS,
			  shift_is_rest int,
				banci_bh VARCHAR(20) COLLATE Chinese_PRC_CI_AS
			)

	    --创建 返回不满足排班的人的原因
			create table #return_table(
				user_serial VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				dept_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
			)

			CREATE TABLE #kt_paiba (
			  user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
			  rq nvarchar(10) COLLATE Chinese_PRC_CI_AS,
			  d1 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d2 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d3 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d4 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d5 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d6 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d7 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d8 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d9 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d10 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d11 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d12 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d13 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d14 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d15 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d16 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d17 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d18 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d19 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d20 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d21 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d22 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d23 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d24 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d25 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d26 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d27 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d28 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d29 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d30 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  d31 varchar(100) COLLATE Chinese_PRC_CI_AS,
			  gly_no nvarchar(10) COLLATE Chinese_PRC_CI_AS,
			  year int ,
			  month int
			)

      CREATE TABLE #scheduleDetail
      (
        user_serial BIGINT NOT NULL,
        col_name  VARCHAR(30),
        shift_id INT NOT NULL DEFAULT (0),
        shift_name VARCHAR(30) NULL,
        shift_date DATE NOT NULL,
        shift_begin INT NOT NULL DEFAULT (0),
        shift_end INT NOT NULL DEFAULT (0),
        shift_sign_begin INT NOT NULL DEFAULT (0),
        shift_sign_end INT NULL,
        shift_late INT NULL,
        shift_early INT NULL,
        shift_duty_on_delay INT NULL,
        shift_duty_off_delay INT NULL,
        shift_workday INT NULL,
        shift_workday_hours INT NULL,
        is_enjoyment_subsidized INT NULL,
        middle_shift_start_time INT NULL,
        middle_shift_end_time INT NULL,
        night_shift_start_time INT NULL,
        night_shift_end_time INT NULL,
        repast_start_time INT NULL,
        repast_end_time INT NULL,
        repast_start_time2 INT NULL,
        repast_end_time2 INT NULL,
        shift_is_rest INT NULL,
				is_have_ot int null,
				is_created_ot int null
      )


		-- 开启事务
			BEGIN TRAN


				-- 拆分用户编号保存临时表中
				insert into #user_serial
				select * from string_split(iif(isnull(@user_serial_str,'')='','',@user_serial_str),',');


				-- 查询班次保存临时表中(这里普通班次默认都是工作日)
				declare @ban_ci_name varchar(50);
				select @ban_ci_name = concat('[A]',isnull(bh,''),'|',isnull(mc,'')) FROM zt_banci t WHERE bh = @ban_ci

				-- select * from A,B 不指定条件那么会产生A的所有B数据，刚好满足需求
				INSERT INTO #result (user_serial, col_name, value, date, year_month,banci_bh)
				select t1.user_serial,t2.*
				from (select * from #user_serial) t1,
				(
					SELECT 'd'+CAST(d.date_day AS VARCHAR(2)) colName, isnull(@ban_ci_name,'') as name,d.date,CONVERT(VARCHAR(7),date,120) date_month,
					@ban_ci as banci_bh
					FROM bd_sys_dim_date d
					WHERE date >= @begin_date AND date <= @end_date
				) t2

				--普通班次排班要根据工作日历，非工作日默认为[X]
				update r
					set r.value = case when (d.is_weekend=1 or d.is_statutory=1) then '[X]'+ SUBSTRING(r.value, 4,len(r.value)) else  r.value end,
							r.shift_is_rest = case when (d.is_weekend=1 or d.is_statutory=1) then 1 else  0 end
				from #result r
				INNER JOIN dt_user u on r.user_serial = u.user_serial
				INNER JOIN bd_kq_work_calendar_detail d on d.parent_id = u.user_work_calendar and r.date = d.work_date

        -- 更新人员信息
        update a set a.user_no = isnull(b.user_no,''), a.user_name = isnull(b.user_lname,''), a.dept_name = isnull(c.dep_name,''), a.dept_serial = isnull(c.dep_serial,'')
        from #result a
        left join dt_user b on a.user_serial = b.user_serial
        left join dt_dep c on b.user_dep = c.dep_serial

				--排班条件校验
        -- 1检测是否为封账状态
        insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
        select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,concat(@begin_date,' —— ',@end_date,' 该时段考勤存在已经封账,不能重新排班')
        from #result a
        left join bd_kq_accounts c on a.user_serial = c.user_serial and year(a.date) = c.year and month(a.date) = c.month
				where isnull(c.lock_status,0)>0
        -- 删除已经封账的数据
        delete a
        from #result a
        inner join #return_table b on a.user_serial = b.user_serial

				--2检测之前是否有排班并且排班有生成的加班单（若该加班单存在，则该人本次全部日期都不能排班）
				insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
        select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,concat(@begin_date,' —— ',@end_date,' 该时段存在排班加班单，不能重新排班')
        from #result a
        inner join bd_kq_schedule_detail d on a.user_serial = d.user_serial and a.date = d.shift_date and d.is_created_ot = 1

        -- 删除已经有加班单的人员数据
        delete a
        from #result a
        inner join #return_table b on a.user_serial = b.user_serial



        --排班明细数据
        INSERT INTO #scheduleDetail (user_serial, col_name, shift_id, shift_name, shift_date, shift_begin, shift_end, shift_sign_begin, shift_sign_end, shift_late, shift_early, shift_duty_on_delay, shift_duty_off_delay, shift_workday, shift_workday_hours, is_enjoyment_subsidized, middle_shift_start_time, middle_shift_end_time, night_shift_start_time, night_shift_end_time, repast_start_time, repast_end_time, repast_start_time2, repast_end_time2, shift_is_rest)
        SELECT user_serial, r.col_name, isnull(bc.id,0), r.value, r.date,isnull(bc.sbsj,0),isnull(bc.xbsj,0),isnull(bc.sbks,0),isnull(bc.xbjs,0),isnull(bc.cdsj,0),isnull(bc.ztsj,0),isnull(bc.bjcd,0),isnull(bc.bjzt,0),isnull(bc.alld,0),isnull(bc.allt,0),isnull(bc.is_enjoyment_subsidized,0),isnull(bc.middle_shift_start_time,0),isnull(bc.middle_shift_end_time,0),isnull(bc.night_shift_start_time,0),isnull(bc.night_shift_end_time,0),isnull(bc.repast_start_time,0),isnull(bc.repast_end_time,0),isnull(bc.repast_start_time2,0),isnull(bc.repast_end_time2,0),r.shift_is_rest
        FROM #result r
				left join (SELECT bc.id, bc.mc, bc.bh, sd.parent, sd.lx, sd.sbsj, sd.xbsj, sd.sbks, sd.xbjs, sd.cdsj, sd.ztsj, sd.bjcd, sd.bjzt, sd.qdqt, sd.sbws, sd.xbws, sd.alld, sd.allt, sd.types, sd.mach, sd.regserial, sd.repast_start_time, sd.repast_end_time, sd.repast_start_time2, sd.repast_end_time2, sd.is_enjoyment_subsidized, sd.middle_shift_start_time, sd.middle_shift_end_time, sd.night_shift_start_time, sd.night_shift_end_time, sd.is_flextime, sd.flextime_interval_begin, sd.flextime_interval_end
        FROM zt_banci bc INNER JOIN zt_shid sd ON bc.bh = sd.parent
        WHERE bc.bh = @ban_ci and sd.lx = 0) bc on r.banci_bh = bc.bh

				--处理	is_have_ot,is_created_ot
				--班次有加班时段，则is_have_ot = 1 ，is_created_ot=0
				--班次没有加班字段 则is_have_ot = 0 ，is_created_ot=0
				update s
					set s.is_have_ot = case when isnull(sd.bh,0)=0 then (case when (isnull(bc.id,0)!=0 and s.shift_is_rest=1) then 1 else 0 end) else 1 end ,
							s.is_created_ot = 0
				from
				#scheduleDetail s
				left join zt_banci bc on bc.id = s.shift_id
				left JOIN zt_shid sd ON bc.bh = sd.parent and sd.lx=1

				--执行保存
				DECLARE @scheduleDetailJson varchar(max)
				select @scheduleDetailJson = (select * from #scheduleDetail for json path)
				select  @scheduleDetailJson,@begin_date,@end_date,@ip,@operator,@doType
        exec bd_proc_action_schedule @scheduleDetailJson,@begin_date,@end_date,@ip,@operator,@doType

				--返回
				select * from #return_table;

		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			END;
			RAISERROR (@errStr, 16, 1);
		END CATCH

--  清除临时表
	drop table #user_serial
	drop table #kt_paiba
	drop table #result
	drop table #return_table
  DROP TABLE #scheduleDetail

END;
go
